package cn.edu.bnu.land.web;


import java.io.IOException;
import java.sql.Connection;
import java.util.Date;//不能使用sql.data，会报错误的，很难查出来
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.mchange.v2.c3p0.ComboPooledDataSource;

@Controller
public class CommonWeb {
	
	ComboPooledDataSource datasource;
	
	Connection conn=null;
	Statement sts=null;
	ResultSet rs=null;

	/**
	 * 返回执法单位列表
	 * @return
	 * @throws IOException
	 */
	@Autowired
	public CommonWeb(ComboPooledDataSource datasource){
		this.datasource=datasource;
	}
	
	@RequestMapping(value="/getZfdw.json*")
	@ResponseBody
	public List<HashMap<String,Object>>handle3(@RequestParam("dm") String dm) throws IOException{
		//ApplicationContext ctx = ContextLoader.getCurrentWebApplicationContext();
		//	ApplicationContext ctx=ApplicationContext.;
		// = (ComboPooledDataSource) ctx.getBean("dataSource"); 

		List<HashMap<String,Object>> list =new ArrayList<HashMap<String,Object>>();
		try {
			conn = datasource.getConnection();
			sts=conn.createStatement();
			rs=sts.executeQuery("select * from ZFDW");//where zfdwdm = '"+dm+"'");
			  while(rs.next()){
				    HashMap<String,Object> map=new HashMap<String,Object>();
				    map.put("name", rs.getString("zfdwmc"));
				    map.put("dm", rs.getString("zfdwdm"));
				    list.add(map);
				    //list.add(rs);
				    }
			    rs.close();
			    sts.close();
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
    }
	
	//查询执法人员
	@RequestMapping(value="/getZfry.json*")
	@ResponseBody
	public List<HashMap<String,Object>>handle4(@RequestParam("dm") String dm) throws IOException{
   // public List<HashMap<String,Object>>handle4() throws IOException{
		//ApplicationContext ctx = ContextLoader.getCurrentWebApplicationContext();
		//	ApplicationContext ctx=ApplicationContext.;
		// = (ComboPooledDataSource) ctx.getBean("dataSource"); 

		List<HashMap<String,Object>> list =new ArrayList<HashMap<String,Object>>();
		try {
			conn = datasource.getConnection();
			sts=conn.createStatement();
			rs=sts.executeQuery("select zfryxm from ZFRY");// where zfdwdm= '"+dm+"'");
			//rs=sts.executeQuery("select zfryxm from ZFRY where zhdwdm="+dm);
			  while(rs.next()){
				    HashMap<String,Object> map=new HashMap<String,Object>();
				    map.put("name", rs.getString("zfryxm"));
				    list.add(map);
				    //list.add(rs);
				    }
			    rs.close();
			    sts.close();
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
    }
	  //巡查结果
	
		@RequestMapping(value = "/getXCResult")
		@ResponseBody
		public HashMap<String, Object> handle2(
				@RequestParam("startTime") Date start,
				@RequestParam("endTime") Date end) throws IOException {
			
			java.sql.Date startD=new java.sql.Date(start.getTime());;
			java.sql.Date endD=new java.sql.Date(end.getTime());;
			HashMap<String, Object> map = new HashMap<String, Object>();
			List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
			try {
				conn = datasource.getConnection();
				sts = conn.createStatement();
				rs = sts.executeQuery("select rwid,dkid,dcjgnr,dcjgzb,wcsj from DCJG  where wcsj"
						+ " between '"+startD+"' and '"+endD+"'");
				// rs=sts.executeQuery("select zfryxm from ZFRY where zhdwdm="+dm);
				while (rs.next()) {
					HashMap<String, Object> map1 = new HashMap<String, Object>();
					map1.put("renwuhao", rs.getString("rwid"));
					map1.put("dikuaihao", rs.getString("dkid"));
					map1.put("zuobiao", rs.getString("dcjgzb"));
					map1.put("result", rs.getString("dcjgnr"));
					map1.put("time", rs.getString("wcsj"));
					list.add(map1);
					// list.add(rs);
				}
				rs.close();
				sts.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			map.put("success", true);
			map.put("info", list);
			return map;
		}
		
		//超期
		@RequestMapping(value = "/getChaoQi")
		@ResponseBody
		public  ArrayList<Object> handle5(
				@RequestParam("fromDate") int days
				) throws IOException {
			
			Date now = new Date(); 
			
			java.sql.Date fromDateD=new java.sql.Date((long) (now.getTime()-days* 24 * 60 * 60 * 1000.0));
            System.out.println(fromDateD+"");
            System.out.println(now.getTime()+"：now.getTime()");
			ArrayList<Object> list = new ArrayList<Object>();
			try {
				conn = datasource.getConnection();
				sts = conn.createStatement();
				rs = sts.executeQuery("select xzqdm,xzqmc,"
						+ "bjzfrybh,bjzfrybz,kssj,jssj from ZFWG where kssj< '"+fromDateD+"'");
				
				
	            System.out.println("查询结果days："+days);
	  		 // SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

				// rs=sts.executeQuery("select zfryxm from ZFRY where zhdwdm="+dm);
				while (rs.next()) {
	
					HashMap<String, Object> map1 = new HashMap<String, Object>();
					map1.put("daima", rs.getString("xzqdm"));
					map1.put("mingcheng", rs.getString("xzqmc"));
					map1.put("bianhao", rs.getString("bjzfrybh"));
					map1.put("beizhu", rs.getString("bjzfrybz"));
					map1.put("starttime", rs.getString("kssj"));
					map1.put("endtime", rs.getString("jssj"));
					list.add(map1);
		        	   //String dateString = formatter.format(format.parse(rs.getString("kssj")));

					// list.add(rs);
				}
				rs.close();
				sts.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return list;
		}
	
	
			
}





